<?php
/**
 * Controller de clientes.
 *
 * @author    Chavão <freela@chavao.net>, Rodrigo Brandão <brandrod@gmail.com>
 * @copyright Copyright (c) 2011, Chavão
 * @version   0.1
 */

class ClienteController extends AppController {
	/**
	 * Método executado antes de qualquer action
	 * @access public
	 */
	public function beforeFilter() {
		if(!$this->Usuario->estaLogado())
			$this->redirect('/usuario/login');
		if(!$this->Usuario->ehMaster())
			$this->redirect('/lead');
	}
	/**
	 * Controller index
	 * @access public
	 */
	public function index( $status = null ) {
		$this->layout = "default.admin";
		$this->bCampoBusca = true;
		$in = ($status == 'removidos') ? '2' : '1,0';
		$sCriterio = "clie_status IN (".$in.")";
		$this->set('showChart',$in);
		// Filtro por busca
		if(trim( $this->param('search')) != '' ) {
			$sCriterio .= " AND (clie_nome LIKE '%".$this->param('search')."%') OR ";
			$sCriterio .= "(clie_empresa LIKE '%".$this->param('search')."%') OR ";
			$sCriterio .= "(clie_cidade LIKE '%".$this->param('search')."%')";
		}
		$arrCliente = $this->Cliente->buscarClientes($sCriterio);
		for($i=0,$j=count($arrCliente);$i<$j;$i++) {
			$plano = $this->Plano->first(array('conditions' => array('plan_id' => $arrCliente[$i]['plan_id'])));
			$arrCliente[$i]['plan_nome'] = $plano['plan_nome'];
			$count_lead = $this->Cliente->fetch("SELECT count(*) as count_lead FROM ldct_lead l INNER JOIN ldct_usuario u ON u.usua_id = l.usua_id WHERE u.clie_id = ".$arrCliente[$i]['clie_id']);
			@$arrCliente[$i]['count_lead'] = $count_lead[0]['count_lead'];
			$count_camp = $this->Cliente->fetch("SELECT count(*) as count_camp FROM ldct_campanha WHERE `clie_id` = ".$arrCliente[$i]['clie_id']);
			@$arrCliente[$i]['count_camp'] = $count_camp[0]['count_camp'];
			switch($arrCliente[$i]['clie_status']) {
				case 2 : @$arrCliente[$i]['status_name'] = 'Excluído'; @$arrCliente[$i]['color'] = '#999'; break;
				case 0 : @$arrCliente[$i]['status_name'] = '<font color="#d00">Inativo</font>'; @$arrCliente[$i]['color'] = '#111'; break;
				default : @$arrCliente[$i]['status_name'] = '<font color="#060">Ativo</font>'; @$arrCliente[$i]['color'] = '#111'; break;
			}
		}
		$arrPlano = $this->Plano->all();
		$this->set('arrPlano', $arrPlano);
		// Setar variáveis para a GUI
		$this->set('arrCliente', $arrCliente);
		$sSliceChart = $this->Cliente->montarJSONGrafico();
		$this->set('sSliceChart',$sSliceChart);
	}
	/**
	 * Controller adicionar
	 * @access public
	 */
	public function adicionar() {
		$this->layout = "default.admin";
		$this->bCampoBusca = false;
		if(!empty( $this->data)) {
			$this->data['clie_data_inicio'] = implode('-',array_reverse(explode('/',$this->data['clie_data_inicio'])));
			$this->data['clie_data_fim'] = implode('-',array_reverse(explode('/',$this->data['clie_data_fim'])));
			$this->Cliente->save($this->data);
			$arrUsua = array(
				'usua_chave' => $this->data['usua_chave'],
				'usua_nome' => $this->data['clie_nome'],
				'usua_email' => $this->data['clie_email'],
				'usua_senha' => md5( $this->data['usua_senha'] ),
				'usua_perm' => 1,
				'clie_id' => $this->Cliente->id
			);
			$this->Usuario->save($arrUsua);
			$this->redirect('/cliente');
		}
		$arrPlano = $this->Plano->all();
		$this->set('arrPlano',$arrPlano);
		$this->set('sUsuaSenha',gerarSenha());
	}
	/**
	 * Controller editar
	 * @access public
	 * @param integer $piClieId Número do ID do cliente para edição
	 */
	public function editar($piClieId = null,$removidos = false) {
		/*
		UPDATE 
			messages
		SET
			test_read = 1
		WHERE 
			id IN(
				SELECT 
					id
				FROM (
					SELECT
						id
					FROM
						messages
					ORDER BY
						date_added DESC
					LIMIT 5,5
			) tmp
		);
		*/
		$this->layout = "default.admin";
		$this->bCampoBusca = false; 
		if(!empty( $this->data)) {
			$this->Cliente->id = $piClieId;
			$this->Cliente->save( $this->data );
			// se mudar o plano do cliente, verificar e bloquear/desbloquear leads limitados
			if($this->data['plan_id_antigo'] != $this->data['plan_id']) {
				$objPlanoNovo =  $this->Plano->first(array(
					'conditions' => array('plan_id' => $this->data['plan_id']) 
				));
				$objPlanoVelho =  $this->Plano->first(array(
					'conditions' => array('plan_id' => $this->data['plan_id_antigo']) 
				));
				if(intval($objPlanoNovo['plan_leads']) > intval($objPlanoVelho['plan_leads'])) {
					$leadModel = new Lead();
					$bLeadBloqueado =  $leadModel->temLeadBloqueado($piClieId);
					
					if($bLeadBloqueado == true) {
						$nLeadsDesbloqueados = $leadModel->quantidadeLeadDesbloqueado($piClieId);
						$liberar = $objPlanoNovo['plan_leads'] - $nLeadsDesbloqueados;
						$arrLeadsBloqueados = $leadModel->leadsBloqueadosCliente($piClieId,$liberar);
 
						foreach($arrLeadsBloqueados as $oLeadBloqueado) {
							$oLeadBloqueado['lead_bloqueado'] = 0;
							$leadModel->save($oLeadBloqueado);
						}
					}
				}
			}
			$redirect = ($removidos == 'removidos') ? '/cliente/index/removidos' : '/cliente';
			$this->redirect($redirect);
		}
		$objCliente = (object) $this->Cliente->first(array('conditions' => array('clie_id' => $piClieId)));
		$arrPlano = $this->Plano->all();
		$this->set('removidos', $removidos);
		$this->set('arrPlano', $arrPlano);
		// Setar variáveis para a GUI
		$this->set('objCliente', $objCliente);
	}
	/**
	 * Controller excluir cliente e todos os dados associados
	 * faz backup de todos os dados em planilha antes
	 * @access public
	 * @param integer $piClieId Número do ID do cliente para exclusão
	 */
	public function excluir( $piClieId = null )
	{
		$this->autoRender = false;
		$this->layout = 'vazio';
		if( $piClieId == null ) $this->redirect('/cliente/index/excluir');
	
		$arrCliente = $this->Cliente->first(array(
										'conditions' => array( 'clie_id='.$piClieId )
									));    
		$arrUsuarios = $this->Usuario->all(array(
										'conditions' => array( 'clie_id='.$piClieId )
									));
		$arrCampanhas = $this->Campanha->all(array(
										'conditions' => array( 'clie_id='.$piClieId )
									));
		$arrLogs = array();
		$arrCompromissos = array();
		$arrComentarios = array();
		$arrCamapanha_campos = array();
		$arrLeads = array();
		?>
		<table border='1' name="ldct_cliente" id="ldct_cliente" style='border-collapse: collapse;'>
	<tr>
			<th style='text-align: center;'>clie_id</th>
			<th style='text-align: center;'>clie_status</th>
			<th style='text-align: center;'>clie_nome</th>
			<th style='text-align: center;'>clie_email</th>
			<th style='text-align: center;'>clie_telefone</th>
			<th style='text-align: center;'>clie_celular</th>
			<th style='text-align: center;'>clie_empresa</th>
			<th style='text-align: center;'>clie_cpf_cnpj</th>
			<th style='text-align: center;'>clie_endereco</th>
			<th style='text-align: center;'>clie_cidade</th>
			<th style='text-align: center;'>clie_estado</th>
			<th style='text-align: center;'>clie_plano</th>
			<th style='text-align: center;'>clie_valor</th>
			<th style='text-align: center;'>clie_max_camp</th>
			<th style='text-align: center;'>clie_max_usua</th>
			<th style='text-align: center;'>clie_max_lead</th>
			<th style='text-align: center;'>clie_data_inicio</th>
			<th style='text-align: center;'>clie_data_fim</th>
			<th style='text-align: center;'>clie_url_acesso</th>
			<th style='text-align: center;'>plan_id</th>
	</tr>
	<?php
		 echo '<tr>
					<td>'.$arrCliente['clie_id'].'</td>
					<td>'.$arrCliente['clie_status'].'</td>
					<td>'.$arrCliente['clie_nome'].'</td>
					<td>'.$arrCliente['clie_email'].'</td>
					<td>'.$arrCliente['clie_telefone'].'</td>
					<td>'.$arrCliente['clie_celular'].'</td>
					<td>'.$arrCliente['clie_empresa'].'</td>
					<td>'.$arrCliente['clie_cpf_cnpj'].'</td>
					<td>'.$arrCliente['clie_endereco'].'</td>
					<td>'.$arrCliente['clie_cidade'].'</td>
					<td>'.$arrCliente['clie_estado'].'</td>
					<td>'.$arrCliente['clie_plano'].'</td>
					<td>'.$arrCliente['clie_valor'].'</td>
					<td>'.$arrCliente['clie_max_camp'].'</td>
					<td>'.$arrCliente['clie_max_usua'].'</td>
					<td>'.$arrCliente['clie_max_lead'].'</td>
					<td>'.$arrCliente['clie_data_inicio'].'</td>
					<td>'.$arrCliente['clie_data_fim'].'</td>
					<td>'.$arrCliente['clie_url_acesso'].'</td>
					<td>'.$arrCliente['plan_id'].'</td>
			   </tr>';
		 $this->Cliente->delete($arrCliente['clie_id']);
		 
	?>
		</table>
		<table border='1' name="ldct_usuario"  id="ldct_usuario" style='border-collapse: collapse;'>
			<tr>
				<th style='text-align: center;'>usua_id</th>
				<th style='text-align: center;'>usua_chave</th>
				<th style='text-align: center;'>usua_nome</th>
				<th style='text-align: center;'>usua_email</th>
				<th style='text-align: center;'>usua_senha</th>
				<th style='text-align: center;'>usua_perm</th>
				<th style='text-align: center;'>clie_id</th>
				<th style='text-align: center;'>usua_ativo</th>
			</tr>
			<?php
			foreach ($arrUsuarios as $oUsuario) 
			{
				$arrLogs = array_merge($arrLogs, $this->LogLeadControl->all(array(
											'conditions' => array( 'usua_id='.$oUsuario['usua_id'] )
										)));
				$arrCompromissos = array_merge($arrCompromissos, $this->Compromisso->all(array(
											'conditions' => array( 'usua_id='.$oUsuario['usua_id'] )
										)));
				$arrComentarios =    array_merge($arrComentarios, $this->Comentario->all(array(
											'conditions' => array( 'usua_id='.$oUsuario['usua_id'] )
										)));
				echo '<tr>
							<td>'.$oUsuario['usua_id'].'</td>
							<td>'.$oUsuario['usua_chave'].'</td>
							<td>'.$oUsuario['usua_nome'].'</td>
							<td>'.$oUsuario['usua_email'].'</td>
							<td>'.$oUsuario['usua_senha'].'</td>
							<td>'.$oUsuario['usua_perm'].'</td>
							<td>'.$oUsuario['clie_id'].'</td>
							<td>'.$oUsuario['usua_ativo'].'</td>
					  </tr>';
			   $this->Usuario->delete($oUsuario['usua_id']);
			}
			?>
		</table>
		
		<table border='1' name="ldct_campanha"  id="ldct_campanha" style='border-collapse: collapse;'>
			<tr>
				<th style='text-align: center;'>camp_id</th>
				<th style='text-align: center;'>camp_nome</th>
				<th style='text-align: center;'>camp_responsavel</th>
				<th style='text-align: center;'>camp_email</th>
				<th style='text-align: center;'>camp_arquivo</th>
				<th style='text-align: center;'>camp_notificar</th>
				<th style='text-align: center;'>camp_dt_criacao</th>
				<th style='text-align: center;'>camp_valor</th>
				<th style='text-align: center;'>clie_id</th>
				<th style='text-align: center;'>camp_url_css</th>
				<th style='text-align: center;'>camp_fila</th>
				<th style='text-align: center;'>camp_email_txt</th>
				<th style='text-align: center;'>camp_ativo</th>
			</tr>
			<?php
			foreach ($arrCampanhas as $oCampanha) 
			{
				$arrCamapanha_campos = array_merge($arrCamapanha_campos, $this->CampanhaCampo->all(array(
											'conditions' => array( 'camp_id='.$oCampanha['camp_id'] )
										)));
				$arrLeads =   array_merge($arrLeads, $this->Lead->all(array(
											'conditions' => array( 'camp_id='.$oCampanha['camp_id'] )
										)));
				echo '<tr>
							<td>'.$oCampanha['camp_id'].'</td>
							<td>'.$oCampanha['camp_nome'].'</td>
							<td>'.$oCampanha['camp_responsavel'].'</td>
							<td>'.$oCampanha['camp_email'].'</td>
							<td>'.$oCampanha['camp_arquivo'].'</td>
							<td>'.$oCampanha['camp_notificar'].'</td>
							<td>'.$oCampanha['camp_dt_criacao'].'</td>
							<td>'.$oCampanha['camp_valor'].'</td>
							<td>'.$oCampanha['clie_id'].'</td>
							<td>'.$oCampanha['camp_url_css'].'</td>
							<td>'.$oCampanha['camp_fila'].'</td>
							<td>'.$oCampanha['camp_email_txt'].'</td>
							<td>'.$oCampanha['camp_ativo'].'</td>

					  </tr>';
				$this->Campanha->delete($oCampanha['camp_id']);
			}
			?>
		</table>
		<table border='1' name="ldct_lead" id="ldct_lead" style='border-collapse: collapse;'>
			<tr>
				<th style='text-align: center;'>lead_id</th>
				<th style='text-align: center;'>lead_nome</th>
				<th style='text-align: center;'>lead_email</th>
				<th style='text-align: center;'>lead_telefone</th>
				<th style='text-align: center;'>lead_celular</th>
				<th style='text-align: center;'>lead_sexo</th>
				<th style='text-align: center;'>lead_nascimento</th>
				<th style='text-align: center;'>lead_endereco</th>
				<th style='text-align: center;'>lead_estado</th>
				<th style='text-align: center;'>lead_cidade</th>
				<th style='text-align: center;'>lead_mensagem</th>
				<th style='text-align: center;'>lead_empresa</th>
				<th style='text-align: center;'>lead_capital</th>
				<th style='text-align: center;'>lead_valor_venda</th>
				<th style='text-align: center;'>lead_profissao</th>
				<th style='text-align: center;'>lead_origem</th>
				<th style='text-align: center;'>camp_id</th>
				<th style='text-align: center;'>lead_entrada</th>
				<th style='text-align: center;'>lead_atendimento</th>
				<th style='text-align: center;'>lead_status</th>
				<th style='text-align: center;'>usua_id</th>
				<th style='text-align: center;'>lead_bloqueado</th>
			</tr>
			<?php
			foreach ($arrLeads as $oLead) 
			{
				 echo '<tr>
							<td>'.$oLead['lead_id'].'</td>
							<td>'.$oLead['lead_nome'].'</td>
							<td>'.$oLead['lead_email'].'</td>
							<td>'.$oLead['lead_telefone'].'</td>
							<td>'.$oLead['lead_celular'].'</td>
							<td>'.$oLead['lead_sexo'].'</td>
							<td>'.$oLead['lead_nascimento'].'</td>
							<td>'.$oLead['lead_endereco'].'</td>
							<td>'.$oLead['lead_estado'].'</td>
							<td>'.$oLead['lead_cidade'].'</td>
							<td>'.$oLead['lead_mensagem'].'</td>
							<td>'.$oLead['lead_empresa'].'</td>
							<td>'.$oLead['lead_capital'].'</td>
							<td>'.$oLead['lead_valor_venda'].'</td>
							<td>'.$oLead['lead_profissao'].'</td>
							<td>'.$oLead['lead_origem'].'</td>
							<td>'.$oLead['camp_id'].'</td>
							<td>'.$oLead['lead_entrada'].'</td>
							<td>'.$oLead['lead_atendimento'].'</td>
							<td>'.$oLead['lead_status'].'</td>
							<td>'.$oLead['usua_id'].'</td>
							<td>'.$oLead['lead_bloqueado'].'</td>
					</tr>';
				 $this->Lead->delete($oLead['lead_id']);
			}
			?>
		</table>
		<table border='1' name="ldct_log" id="ldct_log" style='border-collapse: collapse;'>
			<tr>
				<th style='text-align: center;'>log_id</th>
				<th style='text-align: center;'>log_pagina</th>
				<th style='text-align: center;'>log_acao</th>
				<th style='text-align: center;'>log_info</th>
				<th style='text-align: center;'>log_ip</th>
				<th style='text-align: center;'>log_datahora</th>
				<th style='text-align: center;'>usua_id</th>
			</tr>
			<?php
			foreach ($arrLogs as $oLog) 
			{
				 echo '<tr>
							<td>'.$oLog['log_id'].'</td>
							<td>'.$oLog['log_pagina'].'</td>
							<td>'.$oLog['log_acao'].'</td>
							<td>'.$oLog['log_info'].'</td>
							<td>'.$oLog['log_ip'].'</td>
							<td>'.$oLog['log_datahora'].'</td>
							<td>'.$oLog['usua_id'].'</td>
					</tr>';
				 $this->LogLeadControl->delete($oLog['log_id']);
			}
			?>
		</table>
		<table border='1' name="ldct_comentario" id="ldct_comentario" style='border-collapse: collapse;'>
			<tr>
				<th style='text-align: center;'>come_id</th>
				<th style='text-align: center;'>come_assunto</th>
				<th style='text-align: center;'>come_texto</th>
				<th style='text-align: center;'>come_data</th>
				<th style='text-align: center;'>usua_id</th>
				<th style='text-align: center;'>lead_id</th>
			</tr>
			<?php
			foreach ($arrComentarios as $oComentario) 
			{
				 echo '<tr>
							<td>'.$oComentario['come_id'].'</td>
							<td>'.$oComentario['come_assunto'].'</td>
							<td>'.$oComentario['come_texto'].'</td>
							<td>'.$oComentario['come_data'].'</td>
							<td>'.$oComentario['usua_id'].'</td>
							<td>'.$oComentario['lead_id'].'</td>
					</tr>';
				 $this->Comentario->delete($oComentario['come_id']);
			}
			?>
		</table>
		<table border='1' name="ldct_compromisso" id="ldct_compromisso" style='border-collapse: collapse;'>
			<tr>
				<th style='text-align: center;'>comp_id</th>
				<th style='text-align: center;'>comp_nome</th>
				<th style='text-align: center;'>comp_local</th>
				<th style='text-align: center;'>comp_data</th>
				<th style='text-align: center;'>comp_hora_inicio</th>
				<th style='text-align: center;'>comp_hora_fim</th>
				<th style='text-align: center;'>comp_alerta</th>
				<th style='text-align: center;'>usua_id</th>
			</tr>
			<?php
			foreach ($arrCompromissos as $oCompromisso) 
			{
				 echo '<tr>
							<td>'.$oCompromisso['comp_id'].'</td>
							<td>'.$oCompromisso['comp_nome'].'</td>
							<td>'.$oCompromisso['comp_local'].'</td>
							<td>'.$oCompromisso['comp_data'].'</td>
							<td>'.$oCompromisso['comp_hora_inicio'].'</td>
							<td>'.$oCompromisso['comp_hora_fim'].'</td>
							<td>'.$oCompromisso['comp_alerta'].'</td>
							<td>'.$oCompromisso['usua_id'].'</td>
					</tr>';
				 $this->Compromisso->delete($oCompromisso['comp_id']);
			}
			?>
		</table>
		<table border='1' name="ldct_campanha_campo" id="ldct_campanha_campo" style='border-collapse: collapse;'>
			<tr>
				<th style='text-align: center;'>camp_cmps_id</th>
				<th style='text-align: center;'>camp_id</th>
				<th style='text-align: center;'>cmps_id</th>
			</tr>
			<?php
			foreach ($arrCamapanha_campos as $oCamapanha_campo) 
			{
				 echo '<tr>
							<td>'.$oCamapanha_campo['camp_cmps_id'].'</td>
							<td>'.$oCamapanha_campo['camp_id'].'</td>
							<td>'.$oCamapanha_campo['cmps_id'].'</td>
					   </tr>';
				 $this->CampanhaCampo->delete($oCamapanha_campo['camp_cmps_id']);
			}
			?>
		</table>
		<?php
		$sContent = ob_get_clean();
		$this->salvarXLS( $sContent, $piClieId, $arrCliente['clie_empresa']);
		//$this->redirect('/cliente'); 
	}
   
	private function salvarXLS( $psCorpo, $piClieId, $sClie )
	{
		date_default_timezone_set('America/Sao_Paulo');
		$sCorpo = utf8_decode( $psCorpo );
		$sNomeExcel = 'bkpExclusao_'.$piClieId.'_'.$sClie.'_'.date('d-m-Y');

		header('Expires: 0');
		header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
		header('Cache-Control: no-store, no-cache, must-revalidate');
		header('Cache-Control: post-check=0, pre-check=0', false);
		header('Pragma: no-cache');
		header('Content-type: application/vnd.ms-excel;charset:UTF-8');
		header('Content-Disposition: attachment; filename='.$sNomeExcel.'.xls');
		print "\n";
		echo $sCorpo;
		
	}

	/**
	 * Controller seta o status do cliente para excluido mas mantem todos os dados associados
	 * @access public
	 * @param integer $piClieId Número do ID do cliente para exclusão
	 */
	public function remover($piClieId = null) {
		$this->autoRender = false;
		$this->layout = 'vazio';
		$this->data['clie_status'] = 2;
		$this->Cliente->id = $piClieId;
		$this->Cliente->save($this->data);
		$this->redirect('/cliente'); 
	}

}